﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using MySql.Data;
using MySql.Data.MySqlClient;

namespace pswb.Classes
{
    public enum AuthMode
    {
        WINDOWS,
        SQLSERVER
    }

    class ConnectionBuilder
    {
        public bool SQLTestConnection(string server, AuthMode mode, string database, string user = "", string password = "")
        {
            SqlConnection con = new SqlConnection(SQLConnectionString(server, mode, database, user, password));
            try
            {
                con.Open();
                return true;
            }
            catch (Exception)
            {
                return false;
            }
            finally
            {
                con.Close();
            }
        }

        public List<string> SQLGetDatabases(string server, AuthMode mode, string user = "", string password = "")
        {
            string conStr = "";
            if (mode == AuthMode.SQLSERVER)
            {
                if (user == "")
                    throw new Exception("Username can't be blank");
                conStr = string.Format("Server={0};Database=master;User Id={1};Password={2}", server, user, password);
            }
            else
                conStr = string.Format("Server={0};Database={1};Integrated Security=True", server, "master");
            List<string> databases = new List<string>();
            SqlConnection con = new SqlConnection(conStr);
            try
            {
                con.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = con;
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = "SELECT name FROM sys.sysdatabases";
                SqlDataReader reader = cmd.ExecuteReader();
                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        databases.Add(reader.GetString(0));
                    }
                }
                return databases;
            }
            catch (Exception)
            {
                return databases;
            }
            finally
            {
                con.Close();
            }
        }

        public string SQLConnectionString(string server, AuthMode mode, string database, string user = "", string password = "")
        {
            string conStr = "";
            if (mode == AuthMode.SQLSERVER)
            {
                conStr = string.Format("Server={0};Database={1};User Id={2};Password={3}", server, database, user, password);
            }
            else
                conStr = string.Format("Server={0};Database={1};Integrated Security=True", server, database);
            return conStr;
        }

        public string MySQLConnectionString(string server, string user, string password, string database, int port = 3306)
        {
            return string.Format("server={0};user={1};password={2};database={3};port={4};", server, user, password, database, port);
        }

        public bool MySQLTestConnection(string server, string user, string password, string database = "", int port = 3306)
        {
            string conStr = "";
            if(database == "")
                conStr = string.Format("server={0};user={1};password={2};port={3};", server, user, password, port);
            else
                conStr = string.Format("server={0};user={1};password={2};database={3};port={4};", server, user, password, database, port);
            
            MySqlConnection con = new MySqlConnection(conStr);
            
            try
            {    
                con.Open();
                return true;
            }
            catch (Exception)
            {
                return false;
            }
            finally
            {
                con.Close();
            }
        }

        public List<string> MySQLGetDatabases(string server, string user, string password, int port = 3306)
        {
            string conStr = string.Format("server={0};user={1};password={2};port={3};", server, user, password, port);
            List<string> databases = new List<string>();
            try
            {
                MySqlConnection con = new MySqlConnection(conStr);
                con.Open();
                MySqlCommand cmd = new MySqlCommand();
                cmd.Connection = con;
                cmd.CommandType = System.Data.CommandType.Text;
                cmd.CommandText = "SHOW DATABASES";
                MySqlDataReader reader = cmd.ExecuteReader();
                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        databases.Add(reader.GetString(0));
                    }
                }
            }
            catch (Exception)
            {
                databases = null;
            }
            return databases;
        }
    }
}
